package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.News;
import com.ysd.util.DBManager;

public class NewsDaolmpl {
	public List<News> getNewsList(int nId){
		List<News> list = new ArrayList<News>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs =  null;
		try {
			st = conn.createStatement();
			String sql = "select * from news where newsCategoryid="+nId;
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int newsid = rs.getInt("newsid");
				String newsShiJian = rs.getString("newsShiJian");
				String newsTitle = rs.getString("newsTitle");
				String newsContert = rs.getString("newsContert");
				int newsCategoryid = rs.getInt("newsCategoryid");
				News news = new News(newsid, newsShiJian, newsTitle, newsContert, newsCategoryid);
				list.add(news);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	//fenye
			public List<News> getNewsListBypage(int page,int size){
			List<News> list = new ArrayList<News>();
			Connection conn = DBManager.getConnection();
			Statement st = null;
			ResultSet rs =  null;
			try {
				st = conn.createStatement();
				String sql ="SELECT * FROM news LIMIT "+(page-1)*size+","+size ;
				rs = st.executeQuery(sql);
				while(rs.next()) {
					int newsid = rs.getInt("newsid");
					String newsShiJian = rs.getString("newsShiJian");
					String newsTitle = rs.getString("newsTitle");
					String newsContert = rs.getString("newsContert");
					int newsCategoryid = rs.getInt("newsCategoryid");
					String newsZhuYe = rs.getString("newsZhuYe");
					News news = new News(newsid, newsShiJian, newsTitle, newsContert, newsCategoryid, newsZhuYe);
					list.add(news);
				}
				return list;
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}finally {
				DBManager.closeAll(conn, st, rs);
			}
		}

	public List<News> getNewsDongTai(){
		List<News> list = new ArrayList<News>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs =  null;
		try {
			st = conn.createStatement();
			String sql = "SELECT *from News ORDER BY RAND() LIMIT 4";
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int newsid = rs.getInt("newsid");
				String newsShiJian = rs.getString("newsShiJian");
				String newsTitle = rs.getString("newsTitle");
				String newsContert = rs.getString("newsContert");
				int newsCategoryid = rs.getInt("newsCategoryid");
				String newsZhuYe = rs.getString("newsZhuYe");
				News news = new News(newsid, newsShiJian, newsTitle, newsContert, newsCategoryid, newsZhuYe);
				list.add(news);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	public List<News> getNewsHou(){
		List<News> list = new ArrayList<News>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs =  null;
		try {
			st = conn.createStatement();
			String sql = "SELECT *from News";
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int newsid = rs.getInt("newsid");
				String newsShiJian = rs.getString("newsShiJian");
				String newsTitle = rs.getString("newsTitle");
				String newsContert = rs.getString("newsContert");
				int newsCategoryid = rs.getInt("newsCategoryid");
				String newsZhuYe = rs.getString("newsZhuYe");
				News news = new News(newsid, newsShiJian, newsTitle, newsContert, newsCategoryid, newsZhuYe);
				list.add(news);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	public int delNews(int neId) {
		return DBManager.executeUpdate("DELETE FROM News where newsid="+neId);
		
	}
	/**
	 * 多条件查询资讯
	 * 为啥返回list 应为查询可能会查询多条数据
	 */
	public List<News> getXingWenZiXun(News n,int page,int size){
		List<News> xwList = new ArrayList<News>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs =  null;
		try {
			st = conn.createStatement();
			String sql = "SELECT n. *,nc .newsCategoryName FROM news n,newscategory nc where 1=1 ";//where是永真条件为了解决where拼接问题
			//非空判定
			if (n.getNewsShiJian()!=null && !n.getNewsShiJian().equals("")) {
				sql +=" and newsShiJian LIKE '%"+n.getNewsShiJian()+"%'";
			}
			if (n.getNewsTitle()!=null && !n.getNewsTitle().equals("")) {
				sql +=" and newsTitle LIKE '%"+n.getNewsTitle()+"%'";
			}
			if (n.getNewsContert()!=null && !n.getNewsContert().equals("")) {
				sql +=" and newsContert LIKE '%"+n.getNewsContert()+"%'";
			}
			if (n.getNewsZhuYe()!=null && !n.getNewsZhuYe().equals("")) {
				sql +=" and newsZhuYe LIKE '%"+n.getNewsZhuYe()+"%'";
			}
			if (n.getNewsCategoryid() >0) {
				sql +=" and n.newsCategoryid="+n.getNewsCategoryid();
			}
			sql +=" and n.newsCategoryid=nc.newsCategoryid ";
			if(page>0) {
				sql +=" limit "+(page-1)*size+","+size;
			}
			System.out.println(sql);//打印sql语句
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int newsid = rs.getInt("newsid");
				String newsShiJian = rs.getString("newsShiJian");
				String newsTitle = rs.getString("newsTitle");
				String newsContert = rs.getString("newsContert");
				int newsCategoryid = rs.getInt("newsCategoryid");
				String newsZhuYe = rs.getString("newsZhuYe");
				String newsCategoryName=rs.getString("newsCategoryName");
				News news=new News(newsid, newsShiJian, newsTitle, newsContert, newsCategoryid, newsZhuYe, newsCategoryName);
				xwList.add(news);
			}
			return xwList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	
	/**
	 * 添加
	 */
	public int addNews(News nw) {
		System.out.println(nw);
		return DBManager.executeUpdate("INSERT INTO news VALUES(NULL,'"+nw.getNewsShiJian()+"','"+nw.getNewsTitle()+"','"+nw.getNewsContert()+"','"+nw.getNewsCategoryid()+"','"+nw.getNewsZhuYe()+"')");
	}

	public List<News> getNewsTitles(String title) {
		List<News> ntList = new ArrayList<News>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs =  null;
		try {
			st = conn.createStatement();
			String sql = "SELECT * FROM news WHERE newsTitle='"+title+"'";
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int newsid = rs.getInt("newsid");
				String newsShiJian = rs.getString("newsShiJian");
				String newsTitle = rs.getString("newsTitle");
				String newsContert = rs.getString("newsContert");
				int newsCategoryid = rs.getInt("newsCategoryid");
				String newsZhuYe = rs.getString("newsZhuYe");
				
				News news=new News(newsid, newsShiJian, newsTitle, newsContert, newsCategoryid, newsZhuYe);
				ntList.add(news);
			}
			return ntList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	//回调
	public News getNewsById(int nid) {
		News news=null;
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs =  null;
		try {
			st = conn.createStatement();
			String sql = "SELECT * FROM news WHERE newsid="+nid;
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int newsid = rs.getInt(1);
				String newsShiJian = rs.getString(2);
				String newsTitle = rs.getString(3);
				String newsContert = rs.getString(4);
				int newsCategoryid = rs.getInt(5);
				String newsZhuYe = rs.getString(6);
				
				news=new News(newsid, newsShiJian, newsTitle, newsContert, newsCategoryid, newsZhuYe);
				
			}
			return news;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	//修改
	public int upNewsById(News ns) {
		String sql="UPDATE news SET newsShiJian='"+ns.getNewsShiJian()+"',newsTitle='"+ns.getNewsTitle()+"',newsContert='"+ns.getNewsContert()+"',newsCategoryid="+ns.getNewsCategoryid()+",newsZhuYe='"+ns.getNewsZhuYe()+"' WHERE newsid="+ns.getNewsid();
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	//批量删除
	 public int delNewsByid(String ids) {
		 return DBManager.executeUpdate("DELETE from news WHERE newsid in("+ids+")");
		 
	 }
}
